package com.android.svmc.app.mobile.data;

import java.util.ArrayList;

import com.android.svmc.app.mobile.model.AccountModel;
import com.android.svmc.app.mobile.model.DiaryModel;
import com.android.svmc.app.mobile.model.PostDetail;
import com.android.svmc.app.mobile.model.PostDetailOffline;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseSource extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_POST_OFFLINE = "SEntertainment.db";

	// table wp-posts
	private static final String TABLE_POSTS = "table_posts";
	private static final String ID = "id";
	private static final String ID_POST_SERVER = "id_server";
	private static final String TITLE = "title";
	private static final String CONTENT = "content";
	private static final String CATEGORY = "category";
	private static final String DATE = "date";

	// table diary extent wp-posts
	private static final String TABLE_DIARY = "table_diary";
	private static final String TABLE_ACCOUNT ="table_account";
	private static final String USERNAME = "username";
	private static final String PASSWORD = "pass";
	private static final String SEX      ="sex";
	private static final String BIRTHDAY = "birthday";
	// table bookmark extend diary
	private static final String TABLE_BOOKMARK = "table_bookmark";
	

	private static final String CREATE_TABLE_POSTS = "CREATE TABLE "
			+ TABLE_POSTS + "(" 
			+ ID + " INTEGER PRIMARY KEY," 
			+ ID_POST_SERVER + " TEXT," 
			+ TITLE + " TEXT," 
			+ CONTENT + " TEXT," 
			+ CATEGORY + " TEXT," 
			+ DATE + " TEXT" + ")";
	private static final String CREATE_TABLE_BOOKMARK = "CREATE TABLE "
			+ TABLE_BOOKMARK + "(" 
			+ ID + " INTEGER PRIMARY KEY," 
			+ ID_POST_SERVER + " TEXT," 
			+ TITLE + " TEXT," 
			+ CONTENT + " TEXT," 
			+ CATEGORY + " TEXT," 
			+ DATE + " TEXT" + ")";
	private static final String CREATE_TABLE_DIARY = "CREATE TABLE "
			+ TABLE_DIARY + "(" 
			+ ID + " INTEGER PRIMARY KEY," 
			+ USERNAME + " TEXT," 
			+ TITLE + " TEXT," 
			+ CONTENT + " TEXT," 
			+ DATE + " TEXT" + ")";
	private static final String CREATE_TABLE_ACCOUNT = "CREATE TABLE " 
			+ TABLE_ACCOUNT + "(" 
			+ ID + " INTEGER PRIMARY KEY," 
			+ USERNAME + " TEXT," 
			+ PASSWORD + " TEXT," 
			+ SEX + " TEXT," 
			+ BIRTHDAY + " TEXT" +")";
	
	public DatabaseSource(Context context) {
		super(context, DATABASE_POST_OFFLINE, null, DATABASE_VERSION);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_POSTS);
		db.execSQL(CREATE_TABLE_ACCOUNT);
		db.execSQL(CREATE_TABLE_DIARY);
		db.execSQL(CREATE_TABLE_BOOKMARK);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE_POSTS IF EXISTS " + TABLE_POSTS);
		db.execSQL("DROP TABLE_ACCOUNT IF EXISTS " + TABLE_ACCOUNT);
		db.execSQL("DROP TABLE_DIARY IF EXISTS " + TABLE_DIARY);		
		db.execSQL("DROP TABLE_BOOKMARK IF EXISTS " + TABLE_BOOKMARK);
		onCreate(db);
	}

	/**
	 * add new record to database
	 * 
	 * @param post
	 * @return
	 */
	public long addPost(PostDetailOffline post) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(ID_POST_SERVER, post.getId_server());
		cv.put(TITLE, post.getTitle());
		cv.put(CONTENT, post.getContent());
		cv.put(CATEGORY, post.getCategory());
		cv.put(DATE, post.getDate());
		long rowid = db.insert(TABLE_POSTS, null, cv);
		db.close();
		return rowid;
	}
	/**
	 * add new diary to database
	 * @param : DiaryModel;
	 * @return : id of row is inserted;
	 */
	public long addDiary (DiaryModel diary){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(USERNAME, diary.getName());
		cv.put(TITLE, diary.getTitle());
		cv.put(CONTENT, diary.getContent());
		cv.put(DATE, diary.getDate());
		long rowid = db.insert(TABLE_DIARY, null, cv);
		db.close();
		return rowid;
	}
	/**
	 * delete one diary
	 */
	public int deleteDiary(DiaryModel diary ){
		SQLiteDatabase db = this.getWritableDatabase();
		int indexRow = db.delete(TABLE_DIARY, ID + " =?",
				new String[] { String.valueOf(diary.getId()) });
		db.close();
		return indexRow;
		
	}
	/**
	 *  add new account
	 *  @param: AccountModel
	 *  @return : id of row is inserted
	 */
	public long addAccount (AccountModel account){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(USERNAME, account.getUsername());
		cv.put(PASSWORD, account.getPassword());
		cv.put(SEX, account.getSex());
		cv.put(BIRTHDAY, account.getBirtday());
		long rowid = db.insert(TABLE_ACCOUNT, null, cv);
		db.close();
		return rowid;
	}
	/**
	 *  get all Account from database.
	 * @return
	 */
	public ArrayList<AccountModel> getAllAccount (){
		ArrayList<AccountModel> listAcc = new ArrayList<AccountModel>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_ACCOUNT;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				AccountModel acc = new AccountModel();
				acc.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID))));
				acc.setUsername(cursor.getString(cursor.getColumnIndex(USERNAME)));
				acc.setPassword(cursor.getString(cursor.getColumnIndex(PASSWORD)));
				acc.setBirtday(cursor.getString(cursor.getColumnIndex(BIRTHDAY)));
				acc.setSex(cursor.getString(cursor.getColumnIndex(SEX)));
				listAcc.add(acc);
			}while(cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listAcc;
	}
	/**
	 *  get post by account name
	 *  @param: username
	 */
	public ArrayList<DiaryModel> getDiaryByAccountName(String username){
		ArrayList<DiaryModel> listdiary = new ArrayList<DiaryModel>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_DIARY + " WHERE " + USERNAME + " = " + "'" + username + "'";
		Cursor cursor = db.rawQuery(selectquery, null);
		if(cursor.moveToFirst()){
			do{
				DiaryModel diary = new DiaryModel();
				diary.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(ID))));
				diary.setName(cursor.getString(cursor.getColumnIndex(USERNAME)));
				diary.setContent(cursor.getString(cursor.getColumnIndex(CONTENT)));
				diary.setTitle(cursor.getString(cursor.getColumnIndex(TITLE)));
				diary.setDate(cursor.getString(cursor.getColumnIndex(DATE)));
				listdiary.add(diary);
			}while(cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listdiary;
	}
	public void clearDatabase() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_POSTS, null, null);
		db.close();
	}

	public ArrayList<PostDetail> getPostbyCategory(int idcategory) {
		SQLiteDatabase db = this.getReadableDatabase();
		ArrayList<PostDetail> listpost = new ArrayList<PostDetail>();
		String selectquery = "SELECT *FROM " + TABLE_POSTS + " WHERE "
				+ CATEGORY + " = " + idcategory;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				PostDetail post = new PostDetail();
				post.setId(Integer.parseInt(cursor.getString(cursor
						.getColumnIndex(ID_POST_SERVER))));
				post.setSubtitle(cursor.getString(cursor.getColumnIndex(TITLE)));
				post.setContent(cursor.getString(cursor.getColumnIndex(CONTENT)));
				post.setDatePublish(cursor.getString(cursor
						.getColumnIndex(DATE)));
				listpost.add(post);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listpost;
	}
	/**
	 *  Get all post is marked by used from table bookmark.
	 * @return : arraylist<postdetail>
	 */
	public ArrayList<PostDetail> getAllPostBookMark(){
		SQLiteDatabase db = this.getWritableDatabase();
		ArrayList<PostDetail> listpost = new ArrayList<PostDetail>();
		String selectquery = "SELECT *FROM " + TABLE_BOOKMARK;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				PostDetail post = new PostDetail();
				post.setId(Integer.parseInt(cursor.getString(cursor
						.getColumnIndex(ID_POST_SERVER))));
				post.setSubtitle(cursor.getString(cursor.getColumnIndex(TITLE)));
				post.setContent(cursor.getString(cursor.getColumnIndex(CONTENT)));
				post.setDatePublish(cursor.getString(cursor
						.getColumnIndex(DATE)));
				listpost.add(post);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listpost;
		
	}

	public PostDetail getpPostByIdServer(int id_server) {
		SQLiteDatabase db = this.getWritableDatabase();
		PostDetail post = new PostDetail();
		String selectquery = "SELECT *FROM " + TABLE_POSTS + " WHERE "
				+ ID_POST_SERVER + " = " + id_server;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				post.setId(Integer.parseInt(cursor.getString(cursor
						.getColumnIndex(ID_POST_SERVER))));
				post.setSubtitle(cursor.getString(cursor.getColumnIndex(TITLE)));
				post.setContent(cursor.getString(cursor.getColumnIndex(CONTENT)));
				post.setDatePublish(cursor.getString(cursor
						.getColumnIndex(DATE)));
			}
			cursor.close();
		}
		db.close();
		return post;
	}

}
